﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;


namespace CNotebook.Sales
{
    public class QuoteManager:ConnectionManager
    {
        #region Insert/Update/Delete Line Items
        public void AddQuote(QuoteUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO OQuot (DocNum, DocStatus, PostingDate, DueDate, DocDate, Currency, Location, CardCode, CardName, ContactPerson, Reference, BillTo, ShipTo, SalesMan, Remarks, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount) VALUES " +
                                                        " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Currency, @Location, @CardCode, @CardName, @ContactPerson, @Reference, @BillTo, @ShipTo, @SalesMan, @Remarks, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount)";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));
            oCommand.Parameters.Add(new SqlParameter("@Currency", oUnit.Currency));
            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
            oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
            oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
            oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));

            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT INTO QUOT1 (DocNum, LineNum, ItemCode, ItemDesc, UoM, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalDiscount, Amount) VALUES " +
                                                                "(@DocNum, @LineNum, @ItemCode, @ItemDesc, @UoM, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalDiscount, @Amount)";

            

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@LineNum", Convert.ToInt16(oRow["LineNum"])));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                if (oRow["IsFree"] != DBNull.Value)
                {
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                }
                else
                {
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                }
                oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oRow["TotalDiscount"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                
                oCommand.ExecuteNonQuery();

            }
        }

        public void DeleteLineItems(string DocNumber, int LineNum)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE QUOT1 WHERE DocNum=@DocNum AND LineNum=@LineNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNumber));
            oCommand.Parameters.Add(new SqlParameter("@LineNum", LineNum));

            oCommand.ExecuteNonQuery();

        }

        public void UpdateQuote(QuoteUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OQUOT SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Currency=@Currency, Location=@Location, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, Reference=@Reference, BillTo=@BillTo, ShipTo=@ShipTo, SalesMan=@SalesMan, Remarks=@Remarks, GrossAmount=@GrossAmount, VATAmount=@VATAmount, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount" +
                                                        " WHERE DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));
            oCommand.Parameters.Add(new SqlParameter("@Currency", oUnit.Currency));
            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
            oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));

            oCommand.ExecuteNonQuery();
            
            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE FROM QUOT1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));

            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                
                oCommand.CommandText = "INSERT INTO QUOT1 (DocNum, LineNum, ItemCode, ItemDesc, UoM, Qty, UnitPrice, IsFree, DiscPrcnt, Discount, TotalDiscount, Amount) VALUES " +
                                                            "(@DocNum, @LineNum, @ItemCode, @ItemDesc, @UoM, @Qty, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalDiscount, @Amount)";


                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@LineNum", Convert.ToInt16(oRow["LineNum"])));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                if (oRow["Qty"] == DBNull.Value)
                {
                    int _ZeroValue = 0;
                    oRow["Qty"] = _ZeroValue;
                }
                oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                if (oRow["IsFree"] != DBNull.Value)
                {
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                }
                else
                {
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                }
                oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oRow["TotalDiscount"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.StoredProcedure;
                oCommand.CommandText = "SP_QUOT_UPDATELINENUM";
                oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();
            }
        }
        #endregion

        #region Getting Data

        public DataTable GetQuoteDocument(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT DocNum, PostingDate, CardCode, CardName, DocStatus FROM OQUOT WHERE DocNum LIKE @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataRow GetQuote(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OQUOT WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow  = ds.Tables[0].Rows[0];

            return oRow;
        }


        public DataTable GetQuoteLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM QUOT1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }

        #endregion

    }
}
